﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Timers;
using System.Windows.Forms;
using MediaPortal.GUI.Library;
using MediaPortal.Util;
using MediaPortal.Database;
using MediaPortal.Configuration;
using SQLite.NET;
using System.IO;
using MovingPicturesXML.FTP;
using System.Threading;

namespace MovingPicturesXML {
    // Plugin icons
    [PluginIcons("MovingPicturesXML.Resources.Images.mpxmllogo_48x48.png", 
                 "MovingPicturesXML.Resources.Images.mpxmllogo_48x48_faded.png")]

    public class MovingPicturesXML : IPlugin, ISetupForm {

        // Config variables.
        private bool SaveFile;
        private bool SaveFTP;
        private bool XSLHref;
        private bool CustomXML;
        private bool MakeMovieList;
        private bool MakeStatsNode;
        private bool UseXSLTransform;
        private string CustomXMLList;
        private List<string> columnList = new List<string>();

        private string XSLTransformFile;
        private string XMLCreateInterval;
        private string XMLSavePath;
        private string XMLSaveFileName;
        private string XMLDefaultSortBy;
        private string XMLDefaultSortDir;
        private string FTPServer;
        private string FTPUser;
        private string FTPPass;
        private string FTPPath;
        private string FTPFileName;
        private string XSLLocation;

        // SQL connection
        private SQLiteClient mp_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"movingpictures.db3"));

        // Default cols list
        public static string[] defaultCols = new String[] { "id", "title", "year", "genres", "score", "imdb_id", "videocodec", "videoresolution", "runtime" };
        public static string[] detailedCols = new String[] { "id", "title", "year", "genres", "score", "imdb_id", "videocodec", "videoresolution", "runtime", "fullpath" };
        
        // Timer to make the XML every x time.
        System.Timers.Timer timer = null;

        #region MP Plugin default members
        public string PluginName() {
            return "Moving-PicturesXML";
        }

        public string Description() {
            return "Exports Movie list from Moving-Pictures to XML or HTML";
        }

        public string Author() {
            return "LRFalk01 / Addow";
        }

        public void ShowPlugin() {
            System.Windows.Forms.Form setup = new SetupForm();
            setup.ShowDialog();
        }

        public bool CanEnable() {
            return true;
        }

        public int GetWindowId() {
            return -1; // no window id needed (it is a process plugin)
        }

        public bool DefaultEnabled() {
            return false;
        }

        public bool HasSetup() {
            return true;
        }

        public bool GetHome(out string strButtonText, out string strButtonImage, out string strButtonImageFocus, out string strPictureImage) {
            strButtonText = string.Empty;
            strButtonImage = string.Empty;
            strButtonImageFocus = string.Empty;
            strPictureImage = string.Empty;
            return false;
        }
        #endregion

        #region MP Process Plugin members
        public void Start() {
            this.LoadSettings();
            Double interval = Double.Parse(XMLCreateInterval) * 60000; // default 60 minutes
            timer = new System.Timers.Timer(interval);
            Log.Info("Starting Moving-PicturesXML");
            Log.Info("Moving-PicturesXML: Interval: " + (interval/60000).ToString() + " minutes.");

            bool Conn = this.TestDBConnect();
            if (Conn) {
                Log.Info("Moving-PicturesXML: SQLite test connection success.");
                this.StartTimer();
            }
            else {
                Log.Info("Moving-PicturesXML: SQLite test connection failed.");
                this.Stop();
            }           
        }

        public void Stop() {
            Log.Info("Stopping Moving-PicturesXML");
            mp_db.Close();
            timer.Stop();
        }
        #endregion
        
        // Method to get version of Moving-Pictures used
        private string MovingPicturesVersion() {
            // return MovingPicturesCore.Settings.Version.ToString(); bug in XP?
            System.Reflection.AssemblyName mpAssembly = System.Reflection.AssemblyName.GetAssemblyName(Config.GetFile(Config.Dir.Plugins, @"Windows\MovingPictures.dll"));

            // and show its version		
            return mpAssembly.Version.ToString();
        }
       
        private void StartTimer() {
            timer.Elapsed += timer_Elapsed;
            timer.Start();
        }
 
 
        private void timer_Elapsed(object sender, ElapsedEventArgs e) {
            Log.Info("Moving-PicturesXML: BuildXML");
            this.MakeXML();
        }

        private void LoadSettings() {
            columnList = new List<string>();

            try {
                using (MediaPortal.Profile.Settings xmlreader = new MediaPortal.Profile.Settings("MediaPortal.xml")) {
                    SaveFile = xmlreader.GetValueAsBool("MovingPicturesXML", "SaveFile", false);
                    SaveFTP = xmlreader.GetValueAsBool("MovingPicturesXML", "SaveFTP", false);
                    XSLHref = xmlreader.GetValueAsBool("MovingPicturesXML", "XSLHref", false);
                    CustomXML = xmlreader.GetValueAsBool("MovingPicturesXML", "CustomXML", false);
                    UseXSLTransform = xmlreader.GetValueAsBool("MovingPicturesXML", "UseXSLTransform", false);
                    MakeMovieList = xmlreader.GetValueAsBool("MovingPicturesXML", "MakeMovieList", true);
                    MakeStatsNode = xmlreader.GetValueAsBool("MovingPicturesXML", "MakeStatsNode", true);
                    CustomXMLList = xmlreader.GetValueAsString("MovingPicturesXML", "CustomXMLList", String.Empty);
                    XSLLocation = xmlreader.GetValueAsString("MovingPicturesXML", "XSLLocation", String.Empty);
                    XSLTransformFile = xmlreader.GetValueAsString("MovingPicturesXML", "XSLTransformFile", String.Empty);
                    XMLCreateInterval = xmlreader.GetValueAsString("MovingPicturesXML", "XMLCreateInterval", "60");
                    XMLSavePath = xmlreader.GetValueAsString("MovingPicturesXML", "XMLSavePath", String.Empty);
                    XMLSaveFileName = xmlreader.GetValueAsString("MovingPicturesXML", "XMLSaveFileName", "Moving-PicturesXML.xml");
                    XMLDefaultSortBy = xmlreader.GetValueAsString("MovingPicturesXML", "XMLDefaultSortBy", "sortby");
                    XMLDefaultSortDir = xmlreader.GetValueAsString("MovingPicturesXML", "XMLDefaultSortDir", "ASC");
                    FTPServer = xmlreader.GetValueAsString("MovingPicturesXML", "FTPServer", String.Empty);
                    FTPUser = xmlreader.GetValueAsString("MovingPicturesXML", "FTPUser", String.Empty);
                    FTPPass = xmlreader.GetValueAsString("MovingPicturesXML", "FTPPass", String.Empty);
                    FTPPath = xmlreader.GetValueAsString("MovingPicturesXML", "FTPPath", String.Empty);
                    FTPFileName = xmlreader.GetValueAsString("MovingPicturesXML", "FTPFileName", "Moving-PicturesXML.xml");
                    
                    if (CustomXML) {
                        CustomXMLList = CustomXMLList.Remove(CustomXMLList.LastIndexOf(';'), 1);
                        string[] colList = CustomXMLList.Split(new Char[] { ';' });
                        foreach (string col in colList) {
                            columnList.Add(col);
                        }
                    }
                    else {
                        // default values when customized output is unchecked
                        foreach (string col in defaultCols) {
                            columnList.Add(col);
                        }
                    }
                }
            }
            catch (Exception ex) {
                Log.Error("MovingPicturesXML - LoadSettings(): {0}", ex.Message);
                this.Stop();
            }
        }

        #region XML Handling
        private string GenerateSQL() {
            string selectStatement = "SELECT T1.id, ";
            SQLiteResultSet results = mp_db.Execute("SELECT T1.*, round(T1.score,1) AS score, T2.*, T4.fullpath, T4.videoresolution, T4.videocodec, T4.audiocodec, T4.hassubtitles, T4.videoformat FROM movie_info T1 LEFT OUTER JOIN user_movie_settings T2 ON  T1.id = T2.id LEFT OUTER JOIN local_media__movie_info T3 on T1.id = T3.movie_info_id LEFT OUTER JOIN local_media T4 on T3.local_media_id = T4.id LIMIT 1");
            System.Collections.Hashtable columns = results.ColumnIndices;
            foreach (string col in columnList) {
                if (!columns.ContainsKey(col)) {
                    columnList.Remove(col);
                    Log.Info("Moving-PicturesXML: CustomXML Failure: " + col + " does not exist.");
                    Log.Info("Moving-PicturesXML: CustomXML Failure: " + col + " removed from SQL Query.");
                }
                else {
                    if (col != "user_rating" && col != "watched" && col != "id" && col != "score" && col != "imdb_id") {
                        if (col == "fullpath" || col == "videoresolution" ||
                                col == "videocodec" || col == "audiocodec" || 
                                    col == "hassubtitles" || col == "videoformat") {
                            selectStatement += "T4." + col + ", ";
                        }
                        else {
                            selectStatement += "T1." + col + ", ";
                        }
                    }
                }
            }

            selectStatement += "round(T1.score,1) AS score, T1.imdb_id ";
            selectStatement += "FROM movie_info T1 LEFT OUTER JOIN user_movie_settings T2 ON T1.id = T2.id ";
            selectStatement += "LEFT OUTER JOIN local_media__movie_info T3 on T1.id = T3.movie_info_id ";
            selectStatement += "LEFT OUTER JOIN local_media T4 on T3.local_media_id = T4.id ";
            selectStatement += "WHERE (SELECT COUNT(*) FROM filters__movie_info__black_list T3 WHERE  T1.id = T3.movie_info_id) = 0 ";
            selectStatement += "ORDER BY " + XMLDefaultSortBy + " " + XMLDefaultSortDir + ", T4.fullpath ASC";

            return selectStatement;
        }

        // Main Method to build XML
        public void MakeXML() {            
            XmlDocument xDoc = new XmlDocument();

            XmlNode xNode = xDoc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
            xDoc.AppendChild(xNode);
            if (XSLHref) {
                XmlProcessingInstruction newPI;
                String PItext = "type='text/xsl' href='" + XSLLocation + "'";
                newPI = xDoc.CreateProcessingInstruction("xml-stylesheet", PItext);
                // Add the processing instruction node to the document.
                xDoc.AppendChild(newPI);
            }

            // Add the root element
            XmlElement xElemRoot = xDoc.CreateElement("", "ROOT", "");
            xDoc.AppendChild(xElemRoot);

            XmlElement lastUpdateElement = xDoc.CreateElement("", "LASTUPDATE", "");
            lastUpdateElement.InnerText = DateTime.Now.ToString();
            xElemRoot.AppendChild(lastUpdateElement);

            // root for stats
            if (MakeStatsNode) {
                XmlElement xElemStats = xDoc.CreateElement("", "STATS", "");
                xElemRoot.AppendChild(xElemStats);
                
                // Build stats node
                xDoc = this.MakeXMLStats(xDoc, xElemStats);
            }

            // root for movie list
            if (MakeMovieList) {
                XmlElement xElemMovies = xDoc.CreateElement("", "MOVIES", "");
                xElemRoot.AppendChild(xElemMovies);
                //build movie list
                xDoc = this.MakeXMLMovieList(xDoc, xElemMovies);
            }

            // Transform the xml with xsl if enabled
            if (UseXSLTransform) {
                this.TransformXML(xDoc);
                return;
            }

            // time to deside what to do with the xmlDoc
            // save to local directory
            if (SaveFile) {
                Log.Debug("Moving-PicturesXML: XMLSaveFile: True");
                if (!Directory.Exists(XMLSavePath)) {
                    Directory.CreateDirectory(XMLSavePath);
                }
                xDoc.Save(XMLSavePath + @"\" + XMLSaveFileName);
            }
            else {
                Log.Debug("Moving-PicturesXML: XMLSaveFile: False");
            }

            // save to ftp directory
            if (SaveFTP) {
                Log.Debug("Moving-PicturesXML: XMLFTPFile: True");
                this.SendXMLFTP(xDoc);
            }
            else {
                Log.Debug("Moving-PicturesXML: XMLFTPFile: False");
            }
        }

        private void TransformXML(XmlDocument xdoc) {
            try {
                MemoryStream xmlStream = new MemoryStream();
                System.Xml.Xsl.XslCompiledTransform xmlTransform = new System.Xml.Xsl.XslCompiledTransform();
                xmlTransform.Load(XSLTransformFile);
                xmlTransform.Transform(xdoc, null, xmlStream);
                xmlStream.Flush();
                xmlStream.Position = 0;
                
                if (SaveFile) {
                    FileStream outStream = File.Open(@XMLSavePath + "\\" + XMLSaveFileName,FileMode.Create);
                    xmlStream.WriteTo(outStream);
                    outStream.Flush();
                    outStream.Close();
                }

                if (SaveFTP) {
                    FTPClient ftpC = new FTPClient(FTPServer);
                    ftpC.User(FTPUser);
                    ftpC.Password(FTPPass);
                    ftpC.Put(xmlStream, FTPPath + FTPFileName);
                    ftpC.Quit();
                }

                xmlStream.Close();

            }
            catch (Exception e) {
                Log.Info("Moving-PicturesXML: XML Transform error: " + e.Message);
            }
        }

        // Method to transfer XML file to FTP
        private void SendXMLFTP(XmlDocument xdoc) {
            Log.Debug("Moving-PicturesXML: FTPConnect: " + FTPServer);
            MemoryStream xmlStream = new MemoryStream();
            FTPClient ftpC = new FTPClient(FTPServer);
            ftpC.User(FTPUser);
            ftpC.Password(FTPPass);

            xdoc.Save(xmlStream);
            xmlStream.Flush();
            xmlStream.Position = 0;
            Log.Debug("Moving-PicturesXML: StreamLength: " + xmlStream.Length.ToString());
            ftpC.Put(xmlStream, FTPPath + FTPFileName);
            ftpC.Quit();
        }

        // Method to return Stats Node for XMLDoc
        private XmlDocument MakeXMLStats(XmlDocument xDoc, XmlElement xElemRoot) {
            XmlText xmltext;
            SQLiteResultSet results = this.mp_db.Execute("SELECT COUNT(\"id\") FROM movie_info");

            XmlElement xElemCount = xDoc.CreateElement("", "MOVIECOUNT", "");
            xmltext = xDoc.CreateTextNode(results.Rows[0].fields[0]);
            xElemCount.AppendChild(xmltext);
            xElemRoot.AppendChild(xElemCount);

            XmlElement xElemVersion = xDoc.CreateElement("", "VERSION", "");
            xmltext = xDoc.CreateTextNode(this.MovingPicturesVersion());
            xElemVersion.AppendChild(xmltext);
            xElemRoot.AppendChild(xElemVersion);

            // Most watched query
            results = this.mp_db.Execute(@"
                SELECT movie.title, user.watched 
                FROM movie_info movie
                inner join movie_info__user_movie_settings miums on movie.id = miums.movie_info_id
                inner join user_movie_settings user on miums.user_movie_settings_id = user.id 
                ORDER BY user.watched DESC limit 1
            ");

            XmlElement xElemMostWatched = xDoc.CreateElement("", "MOSTWATCHED", "");
            XmlElement xElemMostWatchedTitle = xDoc.CreateElement("", "TITLE", "");
            XmlElement xElemMostWatchedCount = xDoc.CreateElement("", "WATCHCOUNT", "");

            xmltext = xDoc.CreateTextNode(results.Rows[0].fields[0]);
            xElemMostWatchedTitle.AppendChild(xmltext);
            xElemMostWatched.AppendChild(xElemMostWatchedTitle);
            
            xmltext = xDoc.CreateTextNode(results.Rows[0].fields[1]);
            xElemMostWatchedCount.AppendChild(xmltext);
            xElemMostWatched.AppendChild(xElemMostWatchedCount);

            xElemRoot.AppendChild(xElemMostWatched);

            results = this.mp_db.Execute("SELECT SUM(\"watched\") FROM user_movie_settings");
            XmlElement xElemNumWatched = xDoc.CreateElement("", "MOVIEPLAYS", "");
            xmltext = xDoc.CreateTextNode(results.Rows[0].fields[0]);
            xElemNumWatched.AppendChild(xmltext);
            xElemRoot.AppendChild(xElemNumWatched);

            try {
                // last watched
                results = this.mp_db.Execute(@"
                    select movie.Title, user.Watched, hist.date_watched 
                    from watched_history hist 
                    inner join movie_info movie on hist.movie = movie.id 
                    inner join movie_info__user_movie_settings miums on movie.id = miums.movie_info_id
                    inner join user_movie_settings user on miums.user_movie_settings_id = user.id 
                    order by hist.date_watched desc limit 1");

                XmlElement xElemLastWatched = xDoc.CreateElement("", "LASTWATCHED", "");
                XmlElement xElemLastWatchedTitle = xDoc.CreateElement("", "TITLE", "");
                XmlElement xElemLastWatchedCount = xDoc.CreateElement("", "WATCHCOUNT", "");
                XmlElement xElemLastWatchedTime = xDoc.CreateElement("", "TIMEWATCHED", "");

                if(results.Rows.Count > 0) xmltext = xDoc.CreateTextNode(results.Rows[0].fields[2]);
                else xmltext = xDoc.CreateTextNode("No date set");
                xElemLastWatchedTime.AppendChild(xmltext);
                xElemLastWatched.AppendChild(xElemLastWatchedTime);

                if(results.Rows.Count > 0) xmltext = xDoc.CreateTextNode(results.Rows[0].fields[0]);
                else xmltext = xDoc.CreateTextNode("No movie defined");
                xElemLastWatchedTitle.AppendChild(xmltext);
                xElemLastWatched.AppendChild(xElemLastWatchedTitle);
                
                if(results.Rows.Count > 0) xmltext = xDoc.CreateTextNode(results.Rows[0].fields[1]);
                else xmltext = xDoc.CreateTextNode("No watch count");
                xElemLastWatchedCount.AppendChild(xmltext);
                xElemLastWatched.AppendChild(xElemLastWatchedCount);

                xElemRoot.AppendChild(xElemLastWatched);
            }
            catch {
                // we probably don't have a watched_history table. write empty fields for Last Watched
                XmlElement xElemLastWatched = xDoc.CreateElement("", "LASTWATCHED", "");
                XmlElement xElemLastWatchedTitle = xDoc.CreateElement("", "TITLE", "");
                XmlElement xElemLastWatchedCount = xDoc.CreateElement("", "WATCHCOUNT", "");
                XmlElement xElemLastWatchedTime = xDoc.CreateElement("", "TIMEWATCHED", "");

                xmltext = xDoc.CreateTextNode("");
                xElemLastWatchedTime.AppendChild(xmltext);
                xElemLastWatched.AppendChild(xElemLastWatchedTime);

                xmltext = xDoc.CreateTextNode("");
                xElemLastWatchedTitle.AppendChild(xmltext);
                xElemLastWatched.AppendChild(xElemLastWatchedTitle);

                xmltext = xDoc.CreateTextNode("");
                xElemLastWatchedCount.AppendChild(xmltext);
                xElemLastWatched.AppendChild(xElemLastWatchedCount);

                xElemRoot.AppendChild(xElemLastWatched);
            }

            return xDoc;
        }

        // Method to return MovieList Node for XMLDoc
        private XmlDocument MakeXMLMovieList(XmlDocument xDoc, XmlElement xElemRoot) {
            string selectStatement = this.GenerateSQL();
            SQLiteResultSet results = this.mp_db.Execute(selectStatement);
            System.Collections.Hashtable tableItems = results.ColumnIndices;

            for (int i = 0; i < results.Rows.Count; i++) {
                int j = i; String localfiles = "";
                bool skip = false;

                // distinct selection on IMDB id (if movie has multiple files, merge the local paths, only display 1 movie line)
                if (tableItems.ContainsKey("fullpath")) {
                    localfiles = results.Rows[j].fields[(int)tableItems["fullpath"]];
                    while (j < (results.Rows.Count - 1) && results.Rows[j].fields[(int)tableItems["imdb_id"]].Equals(results.Rows[j + 1].fields[(int)tableItems["imdb_id"]])) {
                        localfiles += "<br/>" + results.Rows[++j].fields[(int)tableItems["fullpath"]];
                    }
                }
                else {
                    if(i > 0 && (results.Rows[i].fields[(int)tableItems["imdb_id"]].Equals(results.Rows[i-1].fields[(int)tableItems["imdb_id"]]))) {
                        skip = true;
                    }
                }

                if (!skip) {
                    XmlText xmltext;
                    XmlElement xElemMov = xDoc.CreateElement("", "MOVIE", "");
                    xElemRoot.AppendChild(xElemMov);

                    foreach (string col in columnList) {
                        XmlElement xElem = xDoc.CreateElement("", col.ToUpper(), "");
                        if (col.ToLower().Equals("fullpath")) {
                            xmltext = xDoc.CreateTextNode(localfiles.Replace("\\", "\\\\").Replace("'", "\\'")); i = j;
                        }
                        else {
                            xmltext = xDoc.CreateTextNode(results.Rows[i].fields[(int)tableItems[col]].Replace("\\", "\\\\").Replace("'", "\\'"));
                        }
                        xElem.AppendChild(xmltext);
                        xElemMov.AppendChild(xElem);
                    }
                }

                // reset bit
                skip = false;
            }
            return xDoc;
        }
        #endregion

        #region Test DB connection
        // Method to test Moving-Pictures DB accessibility.
        private bool TestDBConnect() {
            bool DBConn = false;
            try {
                SQLiteResultSet results = mp_db.Execute("SELECT id FROM movie_info");
                if (results.Rows.Count > 0) {
                    DBConn = true;
                }
            }
            catch (Exception e) {
                Log.Info("Moving-PicturesXML: Failed to connect to DB with error: " + e.Message);
            }
            return DBConn;
        }
        #endregion
    }

}
